package com.coderman.generateSqlBatchTools.testpage;


import com.coderman.generateSqlBatchTools.bean.ColumnBean;
import com.coderman.generateSqlBatchTools.service.GenerateHelper;
import com.coderman.generateSqlBatchTools.service.IGenerateSqlTools;
import com.coderman.generateSqlBatchTools.service.impl.GenerateSqlToolsXLSImpl;
import com.coderman.generateSqlBatchTools.service.impl.GenerateSqlToolsXLSXImpl;
import com.coderman.generateSqlBatchTools.utils.PoiExcelUtils;
import jxl.Sheet;
import jxl.Workbook;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

/**
 * Created by ${fanchunshuai} on 2017-2-5.
 *
 * @version: V1.0
 * @Desc:
 * @Copyright (c) 2017 58到家-版权所有
 */
public class TestSqlBatch {
    private static Logger logger = Logger.getLogger(TestSqlBatch.class);

    private static Workbook rwb = null;
    private static InputStream is = null;

    private static Sheet st = null;


    public static void main(String[] args) {
       /* String filePath = "E:\\test\\djoy员工-需要二级部门.xlsx";

        Workbook rwb = null;
        InputStream is = null;
        int rows;
        Sheet st;
        try {
            is = new FileInputStream(filePath);
            rwb = Workbook.getWorkbook(is);

            st = rwb.getSheet("sheet");
            rows = st.getRows();
            StringBuilder builder = new StringBuilder("");

            for (int k = 1; k < rows; k++) {
                String username =  st.getCell(0, k).getContents().trim();
                System.out.println("username = "+username);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //关闭资源
            rwb.close();
            try {
                is.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }*/
        Map<String, ColumnBean> columnBeanMap = new HashMap<>();
        ColumnBean columnBean = new ColumnBean();
        columnBean.setColumnName("username");
        columnBean.setIndex(0);
        columnBean.setDataType(ColumnBean.ColumnDataType.VARCHAR.getType());
        columnBeanMap.put(columnBean.getColumnName(),columnBean);

        ColumnBean columnBean2 = new ColumnBean();
        columnBean2.setColumnName("email");
        columnBean2.setIndex(1);
        columnBean2.setDataType(ColumnBean.ColumnDataType.VARCHAR.getType());
        columnBeanMap.put(columnBean2.getColumnName(),columnBean2);

        ColumnBean columnBean3 = new ColumnBean();
        columnBean3.setColumnName("chinese_name");
        columnBean3.setIndex(2);
        columnBean3.setDataType(ColumnBean.ColumnDataType.VARCHAR.getType());
        columnBeanMap.put(columnBean3.getColumnName(), columnBean3);

        ColumnBean columnBean4 = new ColumnBean();
        columnBean4.setColumnName("dept2");
        columnBean4.setIndex(3);
        columnBean4.setDataType(ColumnBean.ColumnDataType.VARCHAR.getType());

        columnBeanMap.put(columnBean4.getColumnName(), columnBean4);

        ColumnBean columnBean5 = new ColumnBean();
        columnBean5.setColumnName("dept3");
        columnBean5.setIndex(4);
        columnBean5.setDataType(ColumnBean.ColumnDataType.VARCHAR.getType());
        columnBeanMap.put(columnBean5.getColumnName(),columnBean5);

        ColumnBean columnBean6 = new ColumnBean();
        columnBean6.setColumnName("status");
        columnBean6.setIndex(6);
        columnBean6.setDataType(ColumnBean.ColumnDataType.VARCHAR.getType());
        //columnBeanMap.put(columnBean6.getColumnName(), columnBean6);


        Map<String, ColumnBean> whereBeanMap = new HashMap<>();
        ColumnBean columnBean7 = new ColumnBean();
        columnBean7.setColumnName("username");
        columnBean7.setIndex(0);
        columnBean7.setDataType(ColumnBean.ColumnDataType.VARCHAR.getType());
        whereBeanMap.put(columnBean7.getColumnName(), columnBean7);


        ColumnBean columnBean8 = new ColumnBean();
        columnBean8.setColumnName("email");
        columnBean8.setIndex(1);
        columnBean8.setDataType(ColumnBean.ColumnDataType.VARCHAR.getType());
        whereBeanMap.put(columnBean8.getColumnName(), columnBean8);

        ColumnBean columnBean9 = new ColumnBean();
        columnBean9.setColumnName("product_name");
        columnBean9.setIndex(4);
        columnBean9.setDataType(ColumnBean.ColumnDataType.VARCHAR.getType());
        //whereBeanMap.put(columnBean9.getColumnName(), columnBean9);
        TestSqlBatch sqlBatch = new TestSqlBatch();
        try {

            //boolean b = sqlBatch.generateInsertBatchSql("C:\\Users\\58\\Desktop\\asdf.xlsx",2,"",1,columnBeanMap,"user_staff");
            // System.out.println("b = "+b);
            //PoiExcelUtils.readExcel("C:\\Users\\58\\Desktop\\adb.xlsx",1,3);
            //sqlBatch.generateDeleteBatchSql("C:\\Users\\58\\Desktop\\asdf.xlsx",2,"",1,whereBeanMap,"user_staff");
           // boolean c = sqlBatch.generateUpdateBatchSql("C:\\Users\\58\\Desktop\\asdf.xlsx",2,"",1,columnBeanMap,whereBeanMap,"user_staff");
           // System.out.println("c = "+c);


            IGenerateSqlTools generateSqlTools = new GenerateSqlToolsXLSImpl();
            //boolean a = generateSqlTools.generateInsertBatchSql("C:\\Users\\58\\Desktop\\user2.xls",3,"C:\\Users\\58\\Desktop\\insertxls.txt",1,columnBeanMap,"user_staff");
            //boolean b = generateSqlTools.generateDeleteBatchSql("C:\\Users\\58\\Desktop\\user2.xls",3,"C:\\Users\\58\\Desktop\\deletetxls.txt",1,columnBeanMap,"user_staff");
            //boolean c = generateSqlTools.generateUpdateBatchSql("C:\\Users\\58\\Desktop\\user2.xls",3,"C:\\Users\\58\\Desktop\\updatexls.txt",1,columnBeanMap,whereBeanMap,"user_staff");
            //System.out.println("a = "+a+",b = "+b+",c = "+c);
            IGenerateSqlTools generateSqlTools2 = new GenerateSqlToolsXLSXImpl();
            boolean a = generateSqlTools2.generateInsertBatchSql("C:\\Users\\58\\Desktop\\asdf.xlsx",1,"C:\\Users\\58\\Desktop\\insertxls.txt",1,columnBeanMap,"user_staff");
            boolean b = generateSqlTools2.generateDeleteBatchSql("C:\\Users\\58\\Desktop\\asdf.xlsx",1,"C:\\Users\\58\\Desktop\\deletetxls.txt",1,columnBeanMap,"user_staff");
            boolean c = generateSqlTools2.generateUpdateBatchSql("C:\\Users\\58\\Desktop\\asdf.xlsx",1,"C:\\Users\\58\\Desktop\\updatexls.txt",1,columnBeanMap,whereBeanMap,"user_staff");
            System.out.println("a = "+a+",b = "+b+",c = "+c);


        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}